#!/usr/bin/env python3
"""
MySQL数据库初始化脚本
"""

import pymysql
import sys

def init_mysql_database():
    """初始化MySQL数据库"""
    print("MySQL数据库初始化")
    print("=" * 40)
    
    # 数据库连接配置
    host = 'localhost'
    port = 3306
    user = 'root'
    password = 'mario'
    database = 'ai_ssq'
    
    try:
        # 1. 连接MySQL服务器（不指定数据库）
        print("1. 连接MySQL服务器...")
        connection = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            charset='utf8mb4'
        )
        
        cursor = connection.cursor()
        print("✓ MySQL服务器连接成功")
        
        # 2. 创建数据库
        print("2. 创建数据库...")
        create_db_sql = f"CREATE DATABASE IF NOT EXISTS `{database}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
        cursor.execute(create_db_sql)
        print(f"✓ 数据库 '{database}' 创建成功")
        
        # 3. 使用数据库
        print("3. 切换到目标数据库...")
        cursor.execute(f"USE `{database}`")
        print(f"✓ 已切换到数据库 '{database}'")
        
        # 4. 检查表是否存在
        print("4. 检查现有表...")
        cursor.execute("SHOW TABLES")
        existing_tables = [table[0] for table in cursor.fetchall()]
        print(f"现有表: {existing_tables}")
        
        # 5. 创建用户表
        if 'users' not in existing_tables:
            print("5. 创建用户表...")
            create_users_sql = """
            CREATE TABLE `users` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `username` VARCHAR(80) UNIQUE NOT NULL,
                `password_hash` VARCHAR(120) NOT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """
            cursor.execute(create_users_sql)
            print("✓ 用户表创建成功")
        
        # 6. 创建彩票结果表
        if 'lottery_results' not in existing_tables:
            print("6. 创建彩票结果表...")
            create_lottery_sql = """
            CREATE TABLE `lottery_results` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `draw_date` DATE NOT NULL,
                `draw_number` VARCHAR(20) UNIQUE NOT NULL,
                `red_ball_1` INT NOT NULL,
                `red_ball_2` INT NOT NULL,
                `red_ball_3` INT NOT NULL,
                `red_ball_4` INT NOT NULL,
                `red_ball_5` INT NOT NULL,
                `red_ball_6` INT NOT NULL,
                `blue_ball` INT NOT NULL,
                `prize_info` TEXT,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX `idx_draw_date` (`draw_date`),
                INDEX `idx_draw_number` (`draw_number`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """
            cursor.execute(create_lottery_sql)
            print("✓ 彩票结果表创建成功")
        
        # 7. 创建AI预测表
        if 'ai_predictions' not in existing_tables:
            print("7. 创建AI预测表...")
            create_predictions_sql = """
            CREATE TABLE `ai_predictions` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `start_draw` VARCHAR(20) NOT NULL,
                `end_draw` VARCHAR(20) NOT NULL,
                `data_count` INT NOT NULL,
                `predicted_numbers` TEXT NOT NULL,
                `actual_result` TEXT,
                `win_info` TEXT,
                `win_amount` DECIMAL(10,2) DEFAULT 0,
                `analysis_result` TEXT,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX `idx_created_at` (`created_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """
            cursor.execute(create_predictions_sql)
            print("✓ AI预测表创建成功")
        
        # 8. 创建系统配置表
        if 'system_config' not in existing_tables:
            print("8. 创建系统配置表...")
            create_config_sql = """
            CREATE TABLE `system_config` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `key` VARCHAR(100) UNIQUE NOT NULL,
                `value` TEXT,
                `description` VARCHAR(200),
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """
            cursor.execute(create_config_sql)
            print("✓ 系统配置表创建成功")
        
        # 9. 创建管理员账户
        print("9. 检查管理员账户...")
        cursor.execute("SELECT COUNT(*) FROM users WHERE username = 'admin'")
        admin_count = cursor.fetchone()[0]
        
        if admin_count == 0:
            print("创建管理员账户...")
            from werkzeug.security import generate_password_hash
            admin_password_hash = generate_password_hash('admin123')
            
            insert_admin_sql = """
            INSERT INTO users (username, password_hash) VALUES (%s, %s)
            """
            cursor.execute(insert_admin_sql, ('admin', admin_password_hash))
            print("✓ 管理员账户创建成功 (admin/admin123)")
        else:
            print("✓ 管理员账户已存在")
        
        # 提交更改
        connection.commit()
        
        # 10. 验证表结构
        print("10. 验证表结构...")
        cursor.execute("SHOW TABLES")
        tables = [table[0] for table in cursor.fetchall()]
        print(f"数据库中的表: {tables}")
        
        for table in tables:
            cursor.execute(f"DESCRIBE `{table}`")
            columns = cursor.fetchall()
            print(f"表 '{table}' 的字段:")
            for col in columns:
                print(f"  - {col[0]}: {col[1]}")
            print()
        
        print("=" * 40)
        print("MySQL数据库初始化完成!")
        print(f"数据库: {database}")
        print(f"主机: {host}:{port}")
        print(f"用户: {user}")
        print("管理员账户: admin / admin123")
        
        return True
        
    except pymysql.Error as e:
        print(f"✗ MySQL错误: {e}")
        return False
    except Exception as e:
        print(f"✗ 初始化失败: {e}")
        return False
    finally:
        if 'connection' in locals():
            connection.close()
            print("✓ 数据库连接已关闭")

if __name__ == '__main__':
    success = init_mysql_database()
    if success:
        print("\n现在可以启动后端服务了!")
        print("运行: python run_standalone.py")
    else:
        print("\n数据库初始化失败，请检查MySQL配置")
        sys.exit(1) 